﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;

//using Windows.UI.Xaml.Shapes;

namespace NSR.LocalService.Test
{
  public  class Person
    {
        [SQLite.AutoIncrement, SQLite.PrimaryKey]
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        public  void Create()
        {
            //数据文件保存的位置
            var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db1.sqlite");
            //打开创建数据库和表
            using (var db = new SQLite.SQLiteConnection(dbPath))
            {
                //创建表
                db.CreateTable<Person>();
            }
        }

        public  void Insert()
        {
            //连接数据库
            var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db1.sqlite");
            var db = new SQLite.SQLiteConnection(dbPath);
            //插入操作。首先声明一个集合
            var collection = new ObservableCollection<Person>();
            //单条插入语句
            db.Insert(new Person() { FirstName = "liufei", LastName = "Sky" });

            collection.Add(new Person() { FirstName = "liufei1", LastName = "Sky" });
            collection.Add(new Person() { FirstName = "liufei3", LastName = "Sky" });
            //多条插入集合
            db.InsertAll(collection);
        }

        public  void Update()
        {
            var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db1.sqlite");
            var db = new SQLite.SQLiteConnection(dbPath);

            SQLiteCommand cmd = db.CreateCommand("update person set FirstName='lisa'where FirstName='liufei'");
            cmd.ExecuteNonQuery();
        }

        public  void Delete()
        {
            var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db1.sqlite");
            var db = new SQLite.SQLiteConnection(dbPath);

            db.Delete<Person>(new Person() { ID = 1 });
            //多行删除
            db.DeleteAll<Person>();
        }

        public List<object> Query()
        {
            var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db1.sqlite");
            var db = new SQLite.SQLiteConnection(dbPath);
            List<object> list = db.Query(new TableMapping(typeof (Person)), "select *  from  Person");
            return list;
        }
    }


}
